package com.css.fxfzfxqh.modules.buildingKilometerGrid.repository;

import com.css.fxfzfxqh.modules.buildingKilometerGrid.model.dto.GridSelectByVersionCodeDto;
import com.css.fxfzfxqh.modules.largeScreen.entity.BuildingVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public class FxqhBuildingGridRepository {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public <T> List<T> findAll(String table, Class<T> clz) {
        return jdbcTemplate.query("select *,ST_AsText(geom) geom from " + table + " where del_flag = '0'", new BeanPropertyRowMapper<>(clz));
    }

    public void delBuildingGridTempDatas(String table) {
        StringBuilder sqlBuilder = new StringBuilder("delete from " + table + " where data_version not like '").append("JZWWG").append("%'");
        jdbcTemplate.update(sqlBuilder.toString());
    }

    /**
     * 根据id查询中心点空间数据
     *
     * @param geom
     * @param table
     * @return
     */
    public String selectCenterPoint(String geom) {
//        String sql = "SELECT st_astext(st_centroid(t.geom)) as geom FROM " + table + " t where ST_Within(st_geomfromtext('" + geom + "', 4490),t.geom) and del_flag = '0' order by create_time desc limit 1 offset 0";
        String sql = "SELECT st_astext(st_centroid('" + geom + "')) as geom";
        try {
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public void changeTempToRealData(String table, String sourceVersionCode, String targetVersionCode) {
        String sql = " update " + table + " set data_version = '" + targetVersionCode + "' where data_version = '" + sourceVersionCode + "'";
        jdbcTemplate.update(sql);
    }

    public void delByVersionCode(String table, String versionCode, Date currTime, String currUid) {
        String sql = " update " + table + " set del_flag = '1', update_time = '" + currTime + "', update_user = '" + currUid + "' where data_version = '" + versionCode + "' and del_flag = '0'";
        jdbcTemplate.update(sql);
    }

    public List<GridSelectByVersionCodeDto> selectByVersionCode(String targetVersionCode, String table) {
        String sql = "select id,province,city,county,st_astext(geom) geom from " + table + " where data_version ='" + targetVersionCode + "' and del_flag = '0'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GridSelectByVersionCodeDto.class));
    }

    public void updateExtends1ById(String extend1, String id, String table) {
        if (StringUtils.isBlank(extend1)) {
            return;
        }
        String sql = "update " + table + " set extends1 = '" + extend1 + "' where id = '" + id + "'";
        jdbcTemplate.update(sql);
    }

    public List<BuildingVO> getBuildingCategoryStatistics(String code) {
       StringBuilder sql  = new StringBuilder("select count(*)/1000000 as house_num,sum(total_area) as area,'城镇砌体结构' as structure,'1' as category,'1' as type from fxqh_building_grid_urban_masonry where del_flag='0' ");
        if(StringUtils.isNotBlank(code) && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union select count(*)/1000000 as house_num,sum(total_area) as area,'城镇钢混结构' as structure,'1' as category,'1' as type from fxqh_building_grid_urban_steel_concrete where del_flag='0' ");
        if(StringUtils.isNotBlank(code)  && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union select count(*)/1000000 as house_num,sum(total_area) as area,'城镇钢结构' as structure,'1' as category,'1' as type from fxqh_building_grid_urban_steel where del_flag='0' " );
        if(StringUtils.isNotBlank(code)  && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union select count(*)/1000000 as house_num,sum(total_area) as area,'城镇木结构' as structure,'1' as category,'1' as type from fxqh_building_grid_urban_wood where del_flag='0' ");
        if(StringUtils.isNotBlank(code)  && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union select count(*)/1000000 as house_num,sum(total_area) as area,'城镇其他结构' as structure,'1' as category,'1' as type from fxqh_building_grid_urban_other where del_flag='0' ");
        if(StringUtils.isNotBlank(code)  && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union select count(*)/1000000 as house_num,sum(total_area) as area,'农村砌体结构' as structure,'1' as category,'1' as type from fxqh_building_grid_rural_masonry where del_flag='0' ");
        if(StringUtils.isNotBlank(code)  && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union select count(*)/1000000 as house_num,sum(total_area) as area,'农村土木（石木）结构' as structure,'1' as category,'1' as type from fxqh_building_grid_rural_civil where del_flag='0' ");
        if(StringUtils.isNotBlank(code)  && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union select count(*)/1000000 as house_num,sum(total_area) as area,'农村底部框架结构' as structure,'1' as category,'1' as type from fxqh_building_grid_rural_bottom_frame where del_flag='0' ");
        if(StringUtils.isNotBlank(code)  && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union select count(*)/1000000 as house_num,sum(total_area) as area,'农村木（竹）结构' as structure,'1' as category,'1' as type from fxqh_building_grid_rural_wood_bamboo where del_flag='0' ");
        if(StringUtils.isNotBlank(code)  && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union  select count(*)/1000000 as house_num,sum(total_area) as area,'农村混杂结构' as structure,'1' as category,'1' as type from fxqh_building_grid_rural_mixed where  del_flag='0' ");
        if(StringUtils.isNotBlank(code)  && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union  select count(*)/1000000 as house_num,sum(total_area) as area,'农村钢混结构' as structure,'1' as category,'1' as type from fxqh_building_grid_rural_steel_concrete where del_flag='0' ");
        if(StringUtils.isNotBlank(code)  && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union select count(*)/1000000 as house_num,sum(total_area) as area,'农村钢结构' as structure,'1' as category,'1' as type from fxqh_building_grid_rural_steel where del_flag='0' ");
        if(StringUtils.isNotBlank(code) && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        sql.append(" union select count(*)/1000000 as house_num,sum(total_area) as area,'农村其他结构' as structure,'1' as category,'1' as type from fxqh_building_grid_rural_other where  del_flag='0' ");
        if(StringUtils.isNotBlank(code) && !"0".equals(code)){
            sql.append(" and province_code='").append(code).append("'");
        }
        return jdbcTemplate.query(sql.toString(),new BeanPropertyRowMapper<>(BuildingVO.class));
    }
}
